package com.classicmodels.model;

import com.classicmodels.controller.CustomerController;
import com.classicmodels.view.CustomersList;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;



public class CustomerDAO{
    public static Customer[] all() throws Exception{
        Connection c = Datasource.getConnection();
        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("SELECT * FROM Customers;");
        ArrayList <Customer> customers = new ArrayList <Customer>();
        //String[] customer = new String[12];
        while (rs.next()){
            Customer customer = new Customer();
            customer.setCustomerNumber(rs.getInt("customerNumber"));
            customer.setCustomerName(rs.getString("customerName")); 
            customer.setContactLastName(rs.getString("contactLastName")); 
            customer.setContactFirstName(rs.getString("contactFirstName")); 
            customer.setPhone(rs.getString("phone")); 
            customer.setAdressLine1(rs.getString("addressLine1")); 
            customer.setAdressLine2(rs.getString("addressLine2")); 
            customer.setCity(rs.getString("city")); 
            customer.setState(rs.getString("state")); 
            customer.setPostalCode(rs.getString("postalCode")); 
            customer.setCountry(rs.getString("country")); 
            customer.setSalesRepEmployeeNumber(rs.getInt("salesRepEmployeeNumber"));
            customer.setCreditLimit(rs.getInt("creditLimit"));
            customers.add(customer);
        }
        Customer[] customersArray = new Customer[customers.size()];
        customers.toArray(customersArray);
        return customersArray;
        
    }
         
    public static void save(Customer customer) throws Exception {
    int customerNumber = customer.getCustomerNumber();
    String customerName = customer.getCustomerName();
    String contactLastName = customer.getContactLastName(); 
    String contactFirstName = customer.getContactFirstName(); 
    String phone = customer.getPhone(); 
    String adressLine1 = customer.getAdressLine1(); 
    String adressLine2 = customer.getAdressLine2(); 
    String city = customer.getCity(); 
    String state = customer.getState(); 
    String postalCode = customer.getPostalCode(); 
    String country = customer.getCountry(); 
    int salesRepEmployeeNumber = customer.getSalesRepEmployeeNumber();
    int creditLimit = customer.getCreditLimit();
    
    Connection c = Datasource.getConnection();
    Statement consulta = c.createStatement();
    c.setAutoCommit(false);
    ResultSet rs = consulta.executeQuery("SELECT * FROM Customers WHERE customerNumber = '"+customerNumber+"';");
    if (!rs.next()){
        PreparedStatement st = c.prepareStatement("INSERT INTO Customers(customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
        st.setInt(1, customerNumber);
        st.setString(2, customerName);
        st.setString(3, contactLastName);
        st.setString(4, contactFirstName);
        st.setString(5, phone);
        st.setString(6, adressLine1);
        st.setString(7, adressLine2);
        st.setString(8, city);
        st.setString(9, state);
        st.setString(10, postalCode);
        st.setString(11, country);
        st.setInt(12, salesRepEmployeeNumber);
        st.setInt(13, creditLimit);
        st.executeUpdate();
        c.commit();
    } 
    else {
        PreparedStatement st = c.prepareStatement("UPDATE customers SET customerName = ?, contactLastName = ?, contactFirstName = ?, phone = ?, addressLine1 = ?, addressLine2 = ?, city = ?, state = ?, postalCode = ?, country = ?, salesRepEmployeeNumber = ?, creditLimit = ? WHERE customerNumber = '"+customerNumber+"';");
        st.setString(1, customerName);
        st.setString(2, contactLastName);
        st.setString(3, contactFirstName);
        st.setString(4, phone);
        st.setString(5, adressLine1);
        st.setString(6, adressLine2);
        st.setString(7, city);
        st.setString(8, state);
        st.setString(9, postalCode);
        st.setString(10, country);
        st.setInt(11, salesRepEmployeeNumber);
        st.setInt(12, creditLimit);
        st.executeUpdate();
        c.commit();
    }
}

    public static void delete(int customerNumber) throws Exception{
    Connection c = Datasource.getConnection();
    Statement st = c.createStatement();
    c.setAutoCommit(false);
    st.executeUpdate("DELETE FROM Customers WHERE Customers.customerNumber = '"+customerNumber+"';");
    c.commit();
    CustomerController.show_customers();
}


    public static int getCustomerNumber() throws Exception{
    Connection c = Datasource.getConnection();
    Statement st = c.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM Customers;");
    rs.last();
    int aux = rs.getInt("customerNumber");
    int customerNumber = aux+1;
    return (customerNumber);
}

  
}





    





    
        
    
